package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.ProxyCarInfoDto;

/**
 * 待办车辆daoimp层
 * 
 * @author Administrator
 * 
 */
public class ProxyCarInfoDaoImp extends ViewListDao implements ProxyCarInfoDao {

	protected final String[] sortKeyList = new String[]{"SERIAL","SERIAL","LICENSEPLATE","CARTYPE","IMGPATH","REGDATE","CAROWNER","NEWCAROWNER","ACCEPTEDTIME","STATUS"};//待入库列表排序
	
	protected ProxyCarInfoDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	
	public ProxyCarInfoDaoImp() throws TmsDaoException{
		super();
	}
	/**
	 * 根据车辆临时流水号和时间戳查询车辆是否过期
	 * 
	 * 
	 * 四个月状态小于7
	 * 
	 * 两个月状态小于5
	 */
	@Override
	public int findPassProxyCarInfoBySerial(String serial, String fourMonth,
			int limitStatus) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int updateTime = 0;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT ");
			sql.append("ACCEPTEDTIME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
			sql.append(" WHERE ");
			sql.append(" SERIAL ='" + serial + "' ");
			sql.append(" AND STATUS < " + limitStatus + " ");
			sql.append(" AND STATUS >0  ");
			sql.append(" AND DATE_FORMAT(ACCEPTEDTIME,'%Y-%m-%d') <= '"
					+ fourMonth + "'  ");

			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getString("ACCEPTEDTIME") != null
						|| !"".equals(rs.getString("ACCEPTEDTIME"))) {
					updateTime = 1;// 打
				}
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return updateTime;
	}

	/**
	 * 查询车辆ID是否存在
	 */
	@Override
	public int findProxyCarInfoExistBySerial(String serial)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int updateTime = 0;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT ");
			sql.append(" STATUS ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
			sql.append(" WHERE ");
			sql.append(" SERIAL ='" + serial + "' ");

			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getInt("STATUS") > 0) {
					updateTime = rs.getInt("STATUS");// 打
				}

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return updateTime;
	}

	@Override
	public void insertProxyCarInfo(ProxyCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append("(");
		sql.append(" SERIAL, ");
		sql.append(" VIN, ");
		sql.append(" ENGINE, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" PRICE, ");
		sql.append(" COLOR, ");
		sql.append(" MODELCODE, ");
		sql.append(" VENDORCODE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" DISPLACEMENT, ");
		sql.append(" JQX, ");
		sql.append(" CARUSE, ");
		sql.append(" CARTYPE, ");
		sql.append(" MILEAGE, ");
		sql.append(" GEARBOX, ");
		sql.append(" SENDCARDDATE, ");
		sql.append(" CARDTYPE, ");
		sql.append(" COUNTRYSTYLE, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" VENDORID, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");
		sql.append(" MEMBERID, ");
		sql.append(" FUELTYPE, ");
		sql.append(" LOADQUALITY, ");
		sql.append(" PASSENGER, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" OWNERORGNO, ");
		sql.append(" AGENTNAME, ");
		sql.append(" AGENTOWNERIDCARDNO, ");
		sql.append(" AGENTADDRESS, ");
		sql.append(" AGENTPHONE, ");
		sql.append(" STATUS, ");
		sql.append(" INSERTUSERID, ");
		sql.append(" UPDATETIME, ");
		sql.append(" UPDATEUSERID ");
		sql.append("  ) VALUES ( ");
		sql
				.append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),? )");

		try {
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getVin());
			ps.setString(3, dto.getEngine());
			ps.setString(4, dto.getLicensePlate());
			ps.setInt(5, dto.getPrice());
			ps.setInt(6, dto.getColor());
			ps.setString(7, dto.getModelCode());
			ps.setString(8, dto.getVendorCode());
			ps.setString(9, dto.getRegDate());
			ps.setString(10, dto.getYearCarefulPeriod());
			ps.setInt(11, dto.getDisplacement());
			ps.setString(12, dto.getJqx());
			ps.setInt(13, dto.getCarUse());
			ps.setInt(14, dto.getCarType());
			ps.setInt(15, dto.getMileage());
			ps.setInt(16, dto.getGearbox());
			ps.setString(17, dto.getSendCardDate());
			ps.setInt(18, dto.getCardType());
			ps.setInt(19, dto.getCountryStyle());
			ps.setString(20, dto.getCarOwnerIdCardNo());
			ps.setString(21, dto.getVendorId());
			ps.setString(22, dto.getBrandId());
			ps.setString(23, dto.getCarStyleId());
			ps.setString(24, dto.getMemberId());
			ps.setInt(25, dto.getFuelType());
			ps.setInt(26, dto.getLoadquality());
			ps.setInt(27, dto.getPassenger());
			ps.setInt(28, dto.getOwnerType());
			ps.setString(29, dto.getCarOwner());
			ps.setString(30, dto.getOwnerPhone());
			ps.setString(31, dto.getOwnerAddress());
			ps.setString(32, dto.getOwnerOrgNo());
			ps.setString(33, dto.getAgentName());
			ps.setString(34, dto.getAgentOwnerIdCardNo());
			ps.setString(35, dto.getAgentAddress());
			ps.setString(36, dto.getAgentPhone());
			ps.setInt(37, dto.getStatus());
			ps.setInt(38, dto.getInsertUserId());
			ps.setInt(39, dto.getUpdateUserId());

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

	}

	/**
	 * 待办受理操作员添加待办信息 状态变为未缴费
	 */
	@Override
	public void insertProxyCarInfoByAdmin(ProxyCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append("(");
		sql.append(" SERIAL, ");
		sql.append(" VIN, ");
		sql.append(" ENGINE, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" PRICE, ");
		sql.append(" COLOR, ");
		sql.append(" MODELCODE, ");
		sql.append(" VENDORCODE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" DISPLACEMENT, ");
		sql.append(" JQX, ");
		sql.append(" CARUSE, ");
		sql.append(" CARTYPE, ");
		sql.append(" MILEAGE, ");
		sql.append(" GEARBOX, ");
		sql.append(" SENDCARDDATE, ");
		sql.append(" CARDTYPE, ");
		sql.append(" COUNTRYSTYLE, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" VENDORID, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");
		sql.append(" MEMBERID, ");
		sql.append(" FUELTYPE, ");
		sql.append(" LOADQUALITY, ");
		sql.append(" PASSENGER, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" OWNERORGNO, ");
		sql.append(" AGENTNAME, ");
		sql.append(" AGENTOWNERIDCARDNO, ");
		sql.append(" AGENTADDRESS, ");
		sql.append(" AGENTPHONE, ");
		sql.append(" STATUS, ");
		sql.append(" INSERTUSERID, ");
		sql.append(" UPDATETIME, ");//39
		sql.append(" UPDATEUSERID, ");
		// 操作员部分加
		sql.append(" COMPANYCONTACTPERSON, ");
		sql.append(" CAROWNERIDCARDNOOLD, ");
		sql.append(" OWNERADDRESSOLD, ");
		sql.append(" MOVEOUTPROVINCE, ");
		sql.append(" MOVEOUTCITY, ");
		sql.append(" ACCEPTEDTIME ");

		sql.append("  ) VALUES ( ");
		sql.append(" ?,?,?,?,?,?,?,?,?,?," +
				   " ?,?,?,?,?,?,?,?,?,?," +
				   " ?,?,?,?,?,?,?,?,?,?," +
				   " ?,?,?,?,?,?,?,?,SYSDATE(),?," +
				   " ?,?,?,?,?,SYSDATE() )");

		try {
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getVin());
			ps.setString(3, dto.getEngine());
			ps.setString(4, dto.getLicensePlate());
			ps.setInt(5, dto.getPrice());
			ps.setInt(6, dto.getColor());
			ps.setString(7, dto.getModelCode());
			ps.setString(8, dto.getVendorCode());
			ps.setString(9, dto.getRegDate());
			ps.setString(10, dto.getYearCarefulPeriod());
			ps.setInt(11, dto.getDisplacement());
			ps.setString(12, dto.getJqx());
			ps.setInt(13, dto.getCarUse());
			ps.setInt(14, dto.getCarType());
			ps.setInt(15, dto.getMileage());
			ps.setInt(16, dto.getGearbox());
			ps.setString(17, dto.getSendCardDate());
			ps.setInt(18, dto.getCardType());
			ps.setInt(19, dto.getCountryStyle());
			ps.setString(20, dto.getCarOwnerIdCardNo());
			ps.setString(21, dto.getVendorId());
			ps.setString(22, dto.getBrandId());
			ps.setString(23, dto.getCarStyleId());
			ps.setString(24, dto.getMemberId());
			ps.setInt(25, dto.getFuelType());
			ps.setInt(26, dto.getLoadquality());
			ps.setInt(27, dto.getPassenger());
			ps.setInt(28, dto.getOwnerType());
			ps.setString(29, dto.getCarOwner());
			ps.setString(30, dto.getOwnerPhone());
			ps.setString(31, dto.getOwnerAddress());
			ps.setString(32, dto.getOwnerOrgNo());
			ps.setString(33, dto.getAgentName());
			ps.setString(34, dto.getAgentOwnerIdCardNo());
			ps.setString(35, dto.getAgentAddress());
			ps.setString(36, dto.getAgentPhone());
			ps.setInt(37, 2);
			ps.setInt(38, dto.getUpdateUserId());
			ps.setInt(39, dto.getUpdateUserId());
			ps.setString(40, dto.getCompanyContactPerson());
			ps.setString(41, dto.getCarOwnerIdCardNoOld());
			ps.setString(42, dto.getOwnerAddressOld());
			ps.setString(43, dto.getMoveOutProvince());
			ps.setString(44, dto.getMoveOutCity());
			

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<ProxyCarInfoDto> findProxyCarInfoAll(String memberId,
			String serial, String licensePlate, String timeA, int status,
			String timeB) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<ProxyCarInfoDto> proxyCarList = new ArrayList<ProxyCarInfoDto>();

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" LICENSEPLATE,");
		sql.append(" SERIAL,");
		sql.append(" CARTYPE,");
		sql.append(" MODELCODE,");
		sql.append(" VENDORCODE,");
		sql.append(" REGDATE,");
		sql.append(" CAROWNER,");
		sql.append(" NEWCAROWNER,");
		sql.append(" ACCEPTEDTIME,");
		sql.append(" STATUS");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" MEMBERID='" + memberId + "'");
		if (!"".equals(serial)) {
			sql.append(" AND SERIAL='" + serial + "'");
		}
		if (!"".equals(licensePlate)) {
			sql.append(" AND LICENSEPLATE='" + licensePlate + "'");
		}
		if (!"".equals(timeA) && !"".equals(timeB)) {
			sql.append(" AND ACCEPTEDTIME>='" + timeA + "' AND ACCEPTEDTIME<='"
					+ timeB + "'");
		}
		if (status != 0) {
			sql.append(" AND STATUS=" + status);
		}

		//排序方法
		sql.append(getOrderBySql(sortKeyList));
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));
			// ps.setString(1, memberId);
			// ps.setString(2, serial);
			// ps.setString(3, licensePlate);
			// ps.setString(4, timeA);
			// ps.setString(5, timeB);
			// ps.setInt(6, status);

			rs = ps.executeQuery();
			while (rs.next()) {
				ProxyCarInfoDto dto = new ProxyCarInfoDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setVendorCode(rs.getString("VENDORCODE"));
				dto.setModelCode(rs.getString("MODELCODE"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarOwner(rs.getString("CAROWNER"));
				dto.setNewCarOwner(rs.getString("NEWCAROWNER"));
				dto.setAcceptedTime(rs.getDate("ACCEPTEDTIME"));
				dto.setStatus(rs.getInt("STATUS"));
				proxyCarList.add(dto);

			}

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

		return proxyCarList;
	}

	/**
	 * 
	 * 查询所有待办信息
	 * 
	 */
	@Override
	public ProxyCarInfoDto findProxyCarBySerial(String serial)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ProxyCarInfoDto dto = new ProxyCarInfoDto();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" VIN, ");
		sql.append(" ENGINE, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" COLOR, ");
		sql.append(" MODELCODE, ");
		sql.append(" VENDORCODE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" DISPLACEMENT, ");
		sql.append(" JQX, ");
		sql.append(" CARUSE, ");
		sql.append(" CARTYPE, ");
		sql.append(" MILEAGE, ");
		sql.append(" GEARBOX, ");
		sql.append(" REGISTERCARDNO, ");
		sql.append(" SENDCARDDATE, ");
		sql.append(" CARDTYPE, ");
		sql.append(" COUNTRYSTYLE, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" VENDORID, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");
		sql.append(" MEMBERID, ");
		sql.append(" FUELTYPE, ");
		sql.append(" LOADQUALITY, ");
		sql.append(" PASSENGER, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" OWNERORGNO, ");
		sql.append(" AGENTNAME, ");
		sql.append(" AGENTOWNERIDCARDNO, ");
		sql.append(" AGENTADDRESS, ");
		sql.append(" AGENTPHONE, ");
		sql.append(" ACCEPTEDTIME,");
		sql.append(" STATUS,");
		sql.append(" PRICE,");
		sql.append("NEWAGENTNAME,");
		sql.append("NEWAGENTOWNERIDCARDNO,");
		sql.append("NEWAGENTADDRESS,");
		sql.append("NEWAGENTPHONE,");
		sql.append(" PAYMENTPEOPLE,");
		sql.append(" ZIPCODE, ");
		sql.append(" MAILADDRESS, ");
		sql.append(" GAINTYPE, ");
		sql.append(" MOVEOUTPROVINCE, ");
		sql.append(" MOVEOUTCITY, ");
		sql.append(" TRANSFERTYPE, ");
		sql.append(" CARMARKETIN, ");
		sql.append(" TAXNO, ");
		sql.append(" NEWCAROWNERIDCARDNO, ");
		sql.append(" NEWOWNERTYPE, ");
		sql.append(" NEWOWNERPHONE, ");
		sql.append(" NEWOWNERADDRESS, ");
		sql.append(" NEWOWNERORGNO, ");
		sql.append(" NEWCAROWNER, ");
		
		//录入用户ID，会员信息，预售价格 
		sql.append(" MEMBERID,    ");
		sql.append(" INSERTUSERID, ");
		sql.append(" PRICE,        ");
		sql.append(" AMOUNT,        ");
		sql.append(" OWNERADDRESSOLD,        ");
		sql.append(" CAROWNERIDCARDNOOLD        ");
		
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);

			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setDisplacement(rs.getInt("DISPLACEMENT"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setVin(rs.getString("VIN"));
				dto.setEngine(rs.getString("ENGINE"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
				dto.setFuelType(rs.getInt("FUELTYPE"));
				dto.setLoadquality(rs.getInt("LOADQUALITY"));
				dto.setPassenger(rs.getInt("PASSENGER"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setCarUse(rs.getInt("CARUSE"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
				dto.setJqx(rs.getString("JQX"));
				dto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
				dto.setSendCardDate(rs.getString("SENDCARDDATE"));
				dto.setOwnerType(rs.getInt("OWNERTYPE"));
				dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
				dto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
				dto.setOwnerAddress(rs.getString("OWNERADDRESS"));
				dto.setOwnerPhone(rs.getString("OWNERPHONE"));
				dto.setAgentName(rs.getString("AGENTNAME"));
				dto.setAgentOwnerIdCardNo(rs.getString("AGENTOWNERIDCARDNO"));
				dto.setAgentPhone(rs.getString("AGENTPHONE"));
				dto.setAgentAddress(rs.getString("AGENTADDRESS"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setVendorCode(rs.getString("VENDORCODE"));
				dto.setModelCode(rs.getString("MODELCODE"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarOwner(rs.getString("CAROWNER"));
				dto.setAcceptedTime(rs.getDate("ACCEPTEDTIME"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setNewAgentAddress(rs.getString("NEWAGENTADDRESS"));
				dto.setNewAgentName(rs.getString("NEWAGENTNAME"));
				dto.setNewAgentOwnerIdCardNo(rs.getString("NEWAGENTOWNERIDCARDNO"));
				dto.setNewAgentPhone(rs.getString("NEWAGENTPHONE"));
				dto.setZipCode(rs.getString("ZIPCODE"));
				dto.setMailAddress(rs.getString("MAILADDRESS"));
				dto.setGainType(rs.getInt("GAINTYPE"));
				dto.setPaymentPeople(rs.getString("PAYMENTPEOPLE"));
				dto.setMoveOutCity(rs.getString("MOVEOUTCITY"));
				dto.setMoveOutProvince(rs.getString("MOVEOUTPROVINCE"));
				dto.setTransferType(rs.getInt("TRANSFERTYPE"));
				dto.setCarMarketIn(rs.getString("CARMARKETIN"));
				dto.setTaxNo(rs.getString("TAXNO"));
				dto.setNewcarOwnerIdCardNo(rs.getString("NEWCAROWNERIDCARDNO"));
				dto.setNewOwnerType(rs.getInt("NEWOWNERTYPE"));
				dto.setNewOwnerAddress(rs.getString("NEWOWNERADDRESS"));
				dto.setNewOwnerOrgNo(rs.getString("NEWOWNERORGNO"));
				dto.setNewCarOwner(rs.getString("NEWCAROWNER"));
				dto.setNewOwnerPhone(rs.getString("NEWOWNERPHONE"));
				dto.setMemberId(rs.getString("MEMBERID"));//录入用户ID，会员信息，预售价格 缴费价格
				dto.setInputUserId(rs.getInt("INSERTUSERID"));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setAmount(rs.getInt("AMOUNT"));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setOwnerAddressOld(rs.getString("OWNERADDRESSOLD"));
				dto.setCarOwnerIdCardNoOld(rs.getString("CAROWNERIDCARDNOOLD"));

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

		return dto;
	}

	/**
	 * 
	 * 操作员根据流水号查询车辆基本信息
	 */

	@Override
	public ProxyCarInfoDto findProxyCarInfoBySerial(String serial)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ProxyCarInfoDto dto = new ProxyCarInfoDto();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("SERIAL,");
		sql.append("ACCEPTEDTIME,");
		sql.append("STATUS	,");
		sql.append("GEARBOX,");
		sql.append("DISPLACEMENT,");
		sql.append("LICENSEPLATE,");
		sql.append("VIN,");
		sql.append("VENDORCODE,");
		sql.append("MODELCODE,");
		sql.append("CARTYPE,");
		sql.append("ENGINE,");
		sql.append("COLOR,");
		sql.append("COUNTRYSTYLE,");
		sql.append("FUELTYPE,");
		sql.append("LOADQUALITY,");
		sql.append("PASSENGER,");
		sql.append("MILEAGE,");
		sql.append("CARUSE,");
		sql.append("CARDTYPE,");
		sql.append("YEARCAREFULPERIOD,");
		sql.append("JQX,");
		sql.append("REGISTERCARDNO,");
		sql.append("REGDATE,");
		sql.append("SENDCARDDATE,");
		sql.append("OWNERTYPE,");
		sql.append("CAROWNER,");
		sql.append("CAROWNERIDCARDNO,");
		sql.append("OWNERORGNO,");
		sql.append("OWNERADDRESS,");
		sql.append("OWNERPHONE,");
		sql.append("AGENTNAME,");
		sql.append("AGENTOWNERIDCARDNO,");
		sql.append("AGENTADDRESS,");
		sql.append("AGENTPHONE,");
		sql.append("CAROWNERIDCARDNOOLD,");
		sql.append("OWNERADDRESSOLD,");
		sql.append("MOVEOUTPROVINCE,");
		sql.append("MOVEOUTCITY,");
		sql.append("MEMBERID,");
		sql.append("BRANDID,");
		sql.append("CARSTYLEID,");
		sql.append("VENDORID,");
		sql.append("INSERTUSERID  ");	
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);

			rs = ps.executeQuery();
			while (rs.next()) {
			dto.setSerial(rs.getString("SERIAL"));	
			dto.setAcceptedTime(rs.getDate("ACCEPTEDTIME"));
			dto.setStatus(rs.getInt("STATUS"));	
			dto.setGearbox(rs.getInt("GEARBOX"));
			dto.setDisplacement(rs.getInt("DISPLACEMENT"));
			dto.setLicensePlate(rs.getString("LICENSEPLATE"));
			dto.setVin(rs.getString("VIN"));
			dto.setVendorCode(rs.getString("VENDORCODE"));
			dto.setModelCode(rs.getString("MODELCODE"));
			dto.setCarType(rs.getInt("CARTYPE"));
			dto.setColor(rs.getInt("COLOR"));
			dto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
			dto.setFuelType(rs.getInt("FUELTYPE"));
			dto.setLoadquality(rs.getInt("LOADQUALITY"));
			dto.setPassenger(rs.getInt("PASSENGER"));
			dto.setMileage(rs.getInt("MILEAGE"));
			dto.setCarUse(rs.getInt("CARUSE"));
			dto.setCardType(rs.getInt("CARDTYPE"));
			dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
			dto.setJqx(rs.getString("JQX"));
			dto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
			dto.setRegDate(rs.getString("REGDATE"));
			dto.setSendCardDate(rs.getString("SENDCARDDATE"));
			dto.setOwnerType(rs.getInt("OWNERTYPE"));
			dto.setCarOwner(rs.getString("CAROWNER"));
			dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
			dto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
			dto.setOwnerAddress(rs.getString("OWNERADDRESS"));
			dto.setOwnerPhone(rs.getString("OWNERPHONE"));
			dto.setAgentName(rs.getString("AGENTNAME"));
			dto.setAgentOwnerIdCardNo(rs.getString("AGENTOWNERIDCARDNO"));
			dto.setAgentAddress(rs.getString("AGENTADDRESS"));
			dto.setAgentPhone(rs.getString("AGENTPHONE"));
			dto.setCarOwnerIdCardNoOld(rs.getString("CAROWNERIDCARDNOOLD"));
			dto.setOwnerAddressOld(rs.getString("OWNERADDRESSOLD"));
			dto.setMoveOutProvince(rs.getString("MOVEOUTPROVINCE"));
			dto.setMoveOutCity(rs.getString("MOVEOUTCITY"));
			dto.setMemberId(rs.getString("MEMBERID"));
			dto.setBrandId(rs.getString("BRANDID"));
			dto.setCarStyleId(rs.getString("CARSTYLEID"));
			dto.setVendorId(rs.getString("VENDORID"));
			dto.setInsertUserId(rs.getInt("INSERTUSERID"));
			dto.setEngine(rs.getString("ENGINE"));

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

		return dto;
	}

	@Override
	public void updateProxyCarBySerial(ProxyCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		//sql.append(" SERIAL=?, ");
		sql.append(" VIN=?, ");
		sql.append(" ENGINE=?, ");
		sql.append(" LICENSEPLATE=?, ");
		sql.append(" PRICE=?, ");
		sql.append(" COLOR=?, ");
		sql.append(" MODELCODE=?, ");
		sql.append(" VENDORCODE=?, ");
		sql.append(" REGDATE=?, ");
		sql.append(" YEARCAREFULPERIOD=?, ");
		sql.append(" DISPLACEMENT=?, ");
		sql.append(" JQX=?, ");
		sql.append(" CARUSE=?, ");
		sql.append(" CARTYPE=?, ");
		sql.append(" MILEAGE=?, ");
		sql.append(" GEARBOX=?, ");
		sql.append(" SENDCARDDATE=?, ");
		sql.append(" CARDTYPE=?, ");
		sql.append(" COUNTRYSTYLE=?, ");
		sql.append(" CAROWNERIDCARDNO=?, ");
		sql.append(" VENDORID=?, ");
		sql.append(" BRANDID=?, ");
		sql.append(" CARSTYLEID=?, ");
		//sql.append(" MEMBERID=?, ");
		sql.append(" FUELTYPE=?, ");
		sql.append(" LOADQUALITY=?, ");
		sql.append(" PASSENGER=?, ");
		sql.append(" OWNERTYPE=?, ");
		sql.append(" CAROWNER=?, ");
		sql.append(" OWNERPHONE=?, ");
		sql.append(" OWNERADDRESS=?, ");
		sql.append(" OWNERORGNO=?, ");
		sql.append(" AGENTNAME=?, ");
		sql.append(" AGENTOWNERIDCARDNO=?, ");
		sql.append(" AGENTADDRESS=?, ");
		sql.append(" AGENTPHONE=?, ");
		//sql.append(" STATUS=?, ");
		//sql.append(" INSERTUSERID, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" REGISTERCARDNO=?");
		
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getVin());
			ps.setString(2, dto.getEngine());
			ps.setString(3, dto.getLicensePlate());
			ps.setInt(4, dto.getPrice());
			ps.setInt(5, dto.getColor());
			ps.setString(6, dto.getModelCode());
			ps.setString(7, dto.getVendorCode());
			ps.setString(8, dto.getRegDate());
			ps.setString(9, dto.getYearCarefulPeriod());
			ps.setInt(10, dto.getDisplacement());
			ps.setString(11, dto.getJqx());
			ps.setInt(12, dto.getCarUse());
			ps.setInt(13, dto.getCarType());
			ps.setInt(14, dto.getMileage());
			ps.setInt(15, dto.getGearbox());
			ps.setString(16, dto.getSendCardDate());
			ps.setInt(17, dto.getCardType());
			ps.setInt(18, dto.getCountryStyle());
			ps.setString(19, dto.getCarOwnerIdCardNo());
			ps.setString(20, dto.getVendorId());
			ps.setString(21, dto.getBrandId());
			ps.setString(22, dto.getCarStyleId());
			ps.setInt(23, dto.getFuelType());
			ps.setInt(24, dto.getLoadquality());
			ps.setInt(25, dto.getPassenger());
			ps.setInt(26, dto.getOwnerType());
			ps.setString(27, dto.getCarOwner());
			ps.setString(28, dto.getOwnerPhone());
			ps.setString(29, dto.getOwnerAddress());
			ps.setString(30, dto.getOwnerOrgNo());
			ps.setString(31, dto.getAgentName());
			ps.setString(32, dto.getAgentOwnerIdCardNo());
			ps.setString(33, dto.getAgentAddress());
			ps.setString(34, dto.getAgentPhone());
			ps.setInt(35, dto.getUpdateUserId());
			ps.setString(36, dto.getRegisterCardNo());
			ps.setString(37, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
		
	}


	@Override
	public void updateProxyCarPaidBySerial(ProxyCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		sql.append(" PRICE=?, ");
		sql.append(" MOVEOUTPROVINCE=?, ");
		sql.append(" MOVEOUTCITY=?, ");
		sql.append(" CARMARKETIN=?, ");
		sql.append(" TRANSFERTYPE=?, ");
		sql.append(" TAXNO=?, ");
		sql.append(" PAYMENTPEOPLE=?, ");
		sql.append(" GAINTYPE=?, ");
		sql.append(" NEWOWNERTYPE=?, ");
		sql.append(" NEWOWNERPHONE=?, ");
		sql.append(" NEWOWNERADDRESS=?, ");
		sql.append(" NEWOWNERORGNO=?, ");
		sql.append(" NEWCAROWNERIDCARDNO=?, ");
		sql.append(" NEWAGENTNAME=?, ");
		sql.append(" NEWAGENTOWNERIDCARDNO=?, ");
		sql.append(" NEWAGENTADDRESS=?, ");
		sql.append(" NEWAGENTPHONE=?, ");
		sql.append(" MAILADDRESS=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=? ,");
		sql.append(" ZIPCODE=?, ");
		sql.append(" NEWCAROWNER=? ");
		
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getPrice());
			ps.setString(2, dto.getMoveOutProvince());
			ps.setString(3, dto.getMoveOutCity());
			ps.setString(4, dto.getCarMarketIn());
			ps.setInt(5, dto.getTransferType());
			ps.setString(6, dto.getTaxNo());
			ps.setString(7, dto.getPaymentPeople());
			ps.setInt(8, dto.getGainType());
			ps.setInt(9, dto.getNewOwnerType());
			ps.setString(10, dto.getNewOwnerPhone());
			ps.setString(11, dto.getNewOwnerAddress());
			ps.setString(12, dto.getNewOwnerOrgNo());
			ps.setString(13, dto.getNewcarOwnerIdCardNo());
			ps.setString(14, dto.getNewAgentName());
			ps.setString(15, dto.getNewAgentOwnerIdCardNo());
			ps.setString(16, dto.getNewAgentAddress());
			ps.setString(17, dto.getNewAgentPhone());
			ps.setString(18, dto.getMailAddress());
			ps.setInt(19, dto.getUpdateUserId());
			ps.setString(20, dto.getZipCode());
			ps.setString(21, dto.getNewCarOwner());
			ps.setString(22, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}


	/**
	 * 解锁超期车辆
	 */
	@Override
	public void unchainProxyCarInfoBySerial(String serial, int status,int updateUserId)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		sql.append(" STATUS=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=? ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, status);
			ps.setInt(2, updateUserId);
			ps.setString(3, serial);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
		
	}

	/**
	 * 未受理 ---》受理更新操作
	 * 
	 * 这块根据vin码作为唯一项进行更新
	 * 
	 */
	@Override
	public void updateNCarInfo(ProxyCarInfoDto dto,int bsflag,String lsSerial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		StringBuffer sql = new StringBuffer(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		sql.append(" SERIAL=?, ");
		sql.append(" VIN=?, ");
		sql.append(" ENGINE=?, ");
		sql.append(" LICENSEPLATE=?, ");
		sql.append(" PRICE=?, ");
		sql.append(" COLOR=?, ");
		sql.append(" MODELCODE=?, ");
		sql.append(" VENDORCODE=?, ");
		sql.append(" REGDATE=?, ");
		sql.append(" YEARCAREFULPERIOD=?, ");
		
		sql.append(" DISPLACEMENT=?, ");
		sql.append(" JQX=?, ");
		sql.append(" CARUSE=?, ");
		sql.append(" CARTYPE=?, ");
		sql.append(" MILEAGE=?, ");
		sql.append(" GEARBOX=?, ");
		sql.append(" SENDCARDDATE=?, ");
		sql.append(" CARDTYPE=?, ");
		sql.append(" COUNTRYSTYLE=?, ");
		sql.append(" CAROWNERIDCARDNO=?, ");
		
		sql.append(" VENDORID=?, ");
		sql.append(" BRANDID=?, ");
		sql.append(" CARSTYLEID=?, ");
		sql.append(" MEMBERID=?, ");
		sql.append(" FUELTYPE=?, ");
		sql.append(" LOADQUALITY=?, ");
		sql.append(" PASSENGER=?, ");
		sql.append(" OWNERTYPE=?, ");
		sql.append(" CAROWNER=?, ");
		sql.append(" OWNERPHONE=?, ");

		sql.append(" OWNERADDRESS=?, ");
		sql.append(" OWNERORGNO=?, ");
		sql.append(" AGENTNAME=?, ");
		sql.append(" AGENTOWNERIDCARDNO=?, ");
		sql.append(" AGENTADDRESS=?, ");
		sql.append(" AGENTPHONE=?, ");
		sql.append(" INSERTUSERID=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=?, ");
		// 操作员部分加
		sql.append(" COMPANYCONTACTPERSON=?, ");				
		sql.append(" CAROWNERIDCARDNOOLD=?, ");
		
		sql.append(" OWNERADDRESSOLD=?, ");
		sql.append(" MOVEOUTPROVINCE=?, ");
		sql.append(" MOVEOUTCITY=?, ");
		sql.append(" ACCEPTEDTIME=SYSDATE(), ");
		sql.append(" INSERTUSERID=?, ");
		sql.append(" STATUS=? ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getVin());
			ps.setString(3, dto.getEngine());
			ps.setString(4, dto.getLicensePlate());
			ps.setInt(5, dto.getPrice());
			ps.setInt(6, dto.getColor());
			ps.setString(7, dto.getModelCode());
			ps.setString(8, dto.getVendorCode());
			ps.setString(9, dto.getRegDate());
			ps.setString(10, dto.getYearCarefulPeriod());
			
			ps.setInt(11, dto.getDisplacement());
			ps.setString(12, dto.getJqx());
			ps.setInt(13, dto.getCarUse());
			ps.setInt(14, dto.getCarType());
			ps.setInt(15, dto.getMileage());
			ps.setInt(16, dto.getGearbox());
			ps.setString(17, dto.getSendCardDate());
			ps.setInt(18, dto.getCardType());
			ps.setInt(19, dto.getCountryStyle());
			ps.setString(20, dto.getCarOwnerIdCardNo());
			
			ps.setString(21, dto.getVendorId());
			ps.setString(22, dto.getBrandId());
			ps.setString(23, dto.getCarStyleId());
			ps.setString(24, dto.getMemberId());
			ps.setInt(25, dto.getFuelType());
			ps.setInt(26, dto.getLoadquality());
			ps.setInt(27, dto.getPassenger());
			ps.setInt(28, dto.getOwnerType());
			ps.setString(29, dto.getCarOwner());
			ps.setString(30, dto.getOwnerPhone());
			
			ps.setString(31, dto.getOwnerAddress());
			ps.setString(32, dto.getOwnerOrgNo());
			ps.setString(33, dto.getAgentName());
			ps.setString(34, dto.getAgentOwnerIdCardNo());
			ps.setString(35, dto.getAgentAddress());
			ps.setString(36, dto.getAgentPhone());
			ps.setInt(37, dto.getInsertUserId());
			ps.setInt(38, dto.getUpdateUserId());
			ps.setString(39, dto.getCompanyContactPerson());
			ps.setString(40, dto.getCarOwnerIdCardNoOld());
			
			ps.setString(41, dto.getOwnerAddressOld());
			ps.setString(42, dto.getMoveOutProvince());
			ps.setString(43, dto.getMoveOutCity());
			ps.setInt(44, dto.getUpdateUserId());
			
			ps.setInt(45, bsflag);
			
			ps.setString(46, lsSerial);

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}

	/**
	 * 已交费更新车辆信息
	 */
	@Override
	public void updatePayedProxyCarInfo(ProxyCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		sql.append(" VIN=?, ");
		sql.append(" ENGINE=?, ");
		sql.append(" LICENSEPLATE=?, ");
		sql.append(" PRICE=?, ");
		sql.append(" COLOR=?, ");
		sql.append(" MODELCODE=?, ");
		sql.append(" VENDORCODE=?, ");
		sql.append(" REGDATE=?, ");
		sql.append(" YEARCAREFULPERIOD=?, ");
		sql.append(" DISPLACEMENT=?, ");
		sql.append(" JQX=?, ");
		sql.append(" CARUSE=?, ");
		sql.append(" CARTYPE=?, ");
		sql.append(" MILEAGE=?, ");
		sql.append(" GEARBOX=?, ");
		sql.append(" SENDCARDDATE=?, ");
		sql.append(" CARDTYPE=?, ");
		sql.append(" COUNTRYSTYLE=?, ");
		sql.append(" CAROWNERIDCARDNO=?, ");
		sql.append(" VENDORID=?, ");		
		sql.append(" BRANDID=?, ");
		sql.append(" CARSTYLEID=?, ");
		sql.append(" FUELTYPE=?, ");
		sql.append(" LOADQUALITY=?, ");
		sql.append(" PASSENGER=?, ");
		sql.append(" OWNERTYPE=?, ");
		sql.append(" CAROWNER=?, ");
		sql.append(" OWNERPHONE=?, ");
		sql.append(" OWNERADDRESS=?, ");
		sql.append(" OWNERORGNO=?, ");
		sql.append(" AGENTNAME=?, ");
		sql.append(" AGENTOWNERIDCARDNO=?, ");
		sql.append(" AGENTADDRESS=?, ");
		sql.append(" AGENTPHONE=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=?, ");
		// 操作员部分加
		sql.append(" COMPANYCONTACTPERSON=?, ");
		sql.append(" CAROWNERIDCARDNOOLD=?, ");
		sql.append(" OWNERADDRESSOLD=?, ");
		sql.append(" MOVEOUTPROVINCE=?, ");
		sql.append(" MOVEOUTCITY=?, ");
		sql.append(" ACCEPTEDTIME=SYSDATE(), ");		
		sql.append(" REGISTERCARDNO=?, ");
		sql.append(" NEWAGENTADDRESS=?, ");
		sql.append(" NEWAGENTNAME=?, ");
		sql.append(" NEWAGENTOWNERIDCARDNO=?, ");
		sql.append(" NEWAGENTPHONE=?, ");
		sql.append(" MAILADDRESS=?, ");
		sql.append(" ZIPCODE=?, ");
		sql.append(" GAINTYPE=?, ");
		sql.append(" PAYMENTPEOPLE=?, ");
		sql.append(" TAXNO=?, ");		
		sql.append(" TRANSFERTYPE=?, ");
		sql.append(" NEWCAROWNERIDCARDNO=?, ");
		sql.append(" NEWCAROWNER=?, ");
		sql.append(" NEWOWNERADDRESS=?, ");
		sql.append(" NEWOWNERPHONE=?, ");
		sql.append(" NEWOWNERORGNO=?, ");
		sql.append(" AMOUNT=?, ");
		sql.append(" NEWOWNERTYPE=?  ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getVin());
			ps.setString(2, dto.getEngine());
			ps.setString(3, dto.getLicensePlate());
			ps.setInt(4, dto.getPrice());
			ps.setInt(5, dto.getColor());
			ps.setString(6, dto.getModelCode());
			ps.setString(7, dto.getVendorCode());
			ps.setString(8, dto.getRegDate());
			ps.setString(9, dto.getYearCarefulPeriod());
			ps.setInt(10, dto.getDisplacement());
			ps.setString(11, dto.getJqx());
			ps.setInt(12, dto.getCarUse());
			ps.setInt(13, dto.getCarType());
			ps.setInt(14, dto.getMileage());
			ps.setInt(15, dto.getGearbox());
			ps.setString(16, dto.getSendCardDate());
			ps.setInt(17, dto.getCardType());
			ps.setInt(18, dto.getCountryStyle());
			ps.setString(19, dto.getCarOwnerIdCardNo());
			ps.setString(20, dto.getVendorId());
			ps.setString(21, dto.getBrandId());
			ps.setString(22, dto.getCarStyleId());
			ps.setInt(23, dto.getFuelType());
			ps.setInt(24, dto.getLoadquality());
			ps.setInt(25, dto.getPassenger());
			ps.setInt(26, dto.getOwnerType());
			ps.setString(27, dto.getCarOwner());
			ps.setString(28, dto.getOwnerPhone());
			ps.setString(29, dto.getOwnerAddress());
			ps.setString(30, dto.getOwnerOrgNo());
			ps.setString(31, dto.getAgentName());
			ps.setString(32, dto.getAgentOwnerIdCardNo());
			ps.setString(33, dto.getAgentAddress());
			ps.setString(34, dto.getAgentPhone());
			ps.setInt(35, dto.getUpdateUserId());
			
			ps.setString(36, dto.getCompanyContactPerson());
			ps.setString(37, dto.getCarOwnerIdCardNoOld());
			ps.setString(38, dto.getOwnerAddressOld());
			ps.setString(39, dto.getMoveOutProvince());
			ps.setString(40, dto.getMoveOutCity());
			
			ps.setString(41, dto.getRegisterCardNo());
			ps.setString(42, dto.getNewAgentAddress());
			ps.setString(43, dto.getNewAgentName());
			ps.setString(44, dto.getNewAgentOwnerIdCardNo());
			ps.setString(45, dto.getNewAgentPhone());
			ps.setString(46, dto.getMailAddress());
			ps.setString(47, dto.getZipCode());
			ps.setInt(48, dto.getGainType());
			ps.setString(49, dto.getPaymentPeople());
			ps.setString(50, dto.getTaxNo());
			
			ps.setInt(51, dto.getTransferType());
			ps.setString(52, dto.getNewcarOwnerIdCardNo());
			ps.setString(53, dto.getNewCarOwner());
			ps.setString(54, dto.getNewOwnerAddress());
			ps.setString(55, dto.getNewOwnerPhone());
			ps.setString(56, dto.getNewOwnerOrgNo());
			ps.setInt(57, dto.getAmount());
			ps.setInt(58, dto.getNewOwnerType());
			
			ps.setString(59, dto.getSerial());

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}				
	}


	/**
	 * 
	 * update时间比对
	 */
	@Override
	public int findUpdateTimeBySerial(String serial, String inTime)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int updateTime = 0;
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("UPDATETIME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
			sql.append(" WHERE ");
			sql.append(" SERIAL=? ");
			sql.append("AND UPDATETIME > '"+inTime+"' ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				if(rs.getString("UPDATETIME")!=null && !"".equals(rs.getString("UPDATETIME")))
				{
					updateTime = 1;//打
				}
				
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return updateTime;
	}


	/**
	 * 待办缴费--->更新车辆状态
	 */
	@Override
	public void updatePayedStatusBySerial(String serial, int status,int userId)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer(" UPDATE ");
		sql.append(TmsTableNames.TMS_PROXY_CAR_INFO);
		sql.append(" SET ");
		sql.append(" STATUS=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" UPDATEUSERID=?  ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, status);
			ps.setInt(2, userId);
			ps.setString(3, serial);

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}				
		
	}


}
